package cn.qihua.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import cn.qihua.model.BaseOrder;
import cn.qihua.model.LogOrder;
import cn.qihua.service.ILogService;
import cn.qihua.service.IOrderService;

/**
 * @author Jerry
 * 
 */
public class SyncDatabase {

	private static final String DRIVER_CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	// private static final String URL =
	// "jdbc:sqlserver://61.142.253.194:1433;databaseName=db_fsbaidu";
	private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=db_fsbaidu";

	private IOrderService orderService;
	private ILogService logService;

	private List<BaseOrder> listOrder;
	private List<LogOrder> listFollow;

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;

	private Boolean hasNewOFlag = false;
	private Boolean hasNewFFlag = false;

	private int Nid = 0;
	private int nbid = 0;

	// 连接远程数据库
	public Boolean connect_db_fsbaidu() {
		try {
			Class.forName(DRIVER_CLASS_NAME);
			// this.conn = DriverManager.getConnection(URL, "fsbaidu",
			// "mqocot");
			this.conn = DriverManager.getConnection(URL, "sa", "happy2008");
			this.stmt = this.conn.createStatement();
			System.out.println("connect succeed!");
			return true;
			// rs = stmt.executeQuery("select * from VideoBaseInfo");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			// e.printStackTrace();
		}
		System.out.println("connect failed!");
		return false;
	}

	// 初始化
	public void init() {
		this.setListOrder(); // 获得新的本地数据
		this.setListFollow();
		this.setNbid(); // 获取远程nbid最大数据
		this.setNid(); // 获取远程Nid最大数据
	}

	public void execute() {
		if (!connect_db_fsbaidu()) {
			return;
		}
		this.init(); // ————————- 初始化————————-
		if (hasNewOFlag) {
			for (BaseOrder order : listOrder) {
				int mnbid = nbid + 1;
				order.setNbid(mnbid);
				order.setIsnew(false);
				if (hasNewFFlag) {
					for (LogOrder lo : listFollow) {
						if (lo.getOrderId() == order.getId()) {
							lo.setNbid(mnbid);
						}
					}
				}
				nbid++;
			}
			// this.removeLocalErroneous(); //删除本地错误记录 -- 待测试
			this.updateLocalOrder(); // 更新本地Order记录
		}

		if (hasNewFFlag) {
			for (LogOrder lo : listFollow) {
				if (lo.getIsNew() && lo.getNbid() != null) {
					this.updataRemote(lo);
					lo.setIsNew(false);
				}
			}
			this.updateLocalFollow();// 更新本地Follow记录
		}
		this.clear(); // 最后执行清理工作
	}

	public void updateLocalOrder() {
		for (BaseOrder order : listOrder) {
			System.out.println("updata local Order table:" + order.getId());
			orderService.edit(order);
		}
	}

	public void updateLocalFollow() {
		for (LogOrder lo : listFollow) {
			System.out.println("updata local Follow table:" + lo.getId());
			logService.edit(lo);
		}
	}

	public void removeLocalErroneous() {
		System.out.println("remove Local Follow Erroneous");
		for (LogOrder lo : listFollow) {
			if (lo.getIsNew()) {// &&lo.getNbid=null
				// logService.del(lo.getId());
				listFollow.remove(lo);
			}
		}
	}

	public Boolean updataRemote(BaseOrder Order) {
		String pSQL = "INSERT INTO tabNetBuild (nbid, begintime,ssShoukuantime,company,bdshoukuanmoney,nbcompanymoney,nbmoney,nbcompany,neturl,sellinfo,clientinfo,) VALUES (";
		StringBuffer sb = new StringBuffer();
		sb.append(pSQL);
		sb.append(nbid + 1); // 0
		sb.append(",");
		sb.append("'" + Order.getCreatedate() + "'"); // 1
		sb.append(",");
		sb.append("'" + Order.getCreatedate() + "'"); // 2
		sb.append(",");
		sb.append("'" + Order.getText() + "'"); // 3
		sb.append(",");
		sb.append("'3000'"); // 4
		sb.append(",");
		sb.append("'" + Order.getType() + "'"); // 5
		sb.append(",");
		sb.append(Order.getType()); // 6
		sb.append(",");
		sb.append("'企划动力'"); // 7
		sb.append(",");
		sb.append("'" + Order.getSiteUrl() + "'"); // 8 url
		sb.append(",");
		sb.append("'" + Order.getBaidu() + "'"); // 9
		sb.append(",");
		sb.append("'" + Order.getLinkman() + " " + Order.getPhone() + "'"); // 10

		sb.append(")");
		String SQL = sb.toString();
		try {
			int count = this.stmt.executeUpdate(SQL);
			System.out.println("ROWS AFFECTED: " + count);
			if (count > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		System.out
				.println("updata Remote table tabNetBuild IDENT_CURRENT('tabNetBuild')");
		return false;
	}

	public Boolean updataRemote(LogOrder follow) {
		int mNid = Nid + 1;
		String pSQL = "INSERT INTO tabNetBuild (Nid, nbid,followmemo,indate) VALUES (";
		StringBuffer sb = new StringBuffer();
		sb.append(pSQL);
		sb.append(mNid);
		sb.append(",");
		sb.append(nbid + 1);
		sb.append(",");
		sb.append("'" + follow.getMemo() + "'");
		sb.append(",");
		sb.append("'" + follow.getInDate() + "'");
		sb.append(")");
		String SQL = sb.toString();
		try {
			int count = this.stmt.executeUpdate(SQL);
			System.out.println("ROWS AFFECTED: " + count);
			if (count > 0) {
				Nid++;
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		System.out
				.println("updata Remote table tabNetBuild IDENT_CURRENT('tabNetBuild')");

		return false;
	}

	public void clear() {

		this.nbid = 0;

		this.Nid = 0;

		if (listOrder != null) {
			listOrder = null;
			hasNewOFlag = false;
		}
		if (listFollow != null) {
			listFollow = null;
			hasNewFFlag = false;
		}
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}

			if (stmt != null) {
				stmt.close();
				stmt = null;
			}

			if (conn != null) {
				conn.close();
				conn = null;
			}
			System.out.println("Connect closed!");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void setListOrder() {
		this.listOrder = orderService.findByProperty("isnew", true); // 获得更新
		if (listOrder != null && listOrder.size() > 0) {
			this.setHasNewOFlag(true);
		} else {
			this.setHasNewFFlag(false);
		}
	}

	public void setListFollow() {
		this.listFollow = logService.findLogOrderByProperty("isNew", true);
		if (listFollow != null && listFollow.size() > 0) {
			this.setHasNewFFlag(true);
		} else {
			this.setHasNewFFlag(false);
		}
	}

	public Boolean getHasNewOFlag() {
		return hasNewOFlag;
	}

	public void setHasNewOFlag(Boolean hasNewOFlag) {
		this.hasNewOFlag = hasNewOFlag;
	}

	public Boolean getHasNewFFlag() {
		return hasNewFFlag;
	}

	public void setHasNewFFlag(Boolean hasNewFFlag) {
		this.hasNewFFlag = hasNewFFlag;
	}

	public void setOrderService(IOrderService orderService) {
		this.orderService = orderService;
	}

	public void setLogService(ILogService logService) {
		this.logService = logService;
	}

	public int getNid() {
		return Nid;
	}

	public void setNid() {
		try {
			this.rs = this.stmt
					.executeQuery("SELECT MAX(Nid) FROM tabNetFollow");
			this.Nid = rs.getInt(0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int getNbid() {
		return nbid;
	}

	public void setNbid() {
		try {
			this.rs = this.stmt
					.executeQuery("SELECT MAX(nbid) FROM tabNetBuild");
			this.nbid = rs.getInt(0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
